/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.TourBooking;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class TourBookingBean {
    Connect con=new Connect();    
    Connection conn;
    Collection getAllTourBooking;
    Collection getAllTourCancel;
    Collection getAllTourBook;
    Collection getBookingId;
    Collection getBillCancel;
    Collection getBillBookTour;
    CallableStatement cl=null;
    
    /** Creates a new instance of TourBookingBean */
    public TourBookingBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public boolean deleteTourBooking(String idBooking) {
        
        int row;
        try {
            cl=conn.prepareCall("{call deleteTourBooking_sp(?)}");
            cl.setInt(1,Integer.valueOf(idBooking));
            row=cl.executeUpdate();            
            if(row>0) {
                return true;
            }
            closeCnn();
        } catch (Exception ex) {
            // ex.printStackTrace();
            closeCnn();
            return false;
        }
        return false;
    }
    
    public Collection getAllTourBooking() {
        
        getAllTourBooking=new ArrayList();
        conn=con.getConnection();
        try {
            
            cl=conn.prepareCall("{call getAllTourBooking_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                TourBooking tourAllBooking=new TourBooking();
                
                tourAllBooking.setBookingId(rs.getInt("BookingID"));
                tourAllBooking.setUserName(rs.getString("UserName"));
                tourAllBooking.setTourId(rs.getString("TourID"));
                tourAllBooking.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                tourAllBooking.setTypeTour(rs.getString("TypeTour"));
                tourAllBooking.setTotalMoney(rs.getFloat("TotalMoney"));
                tourAllBooking.setStartDate(rs.getString("StartDate"));
                tourAllBooking.setEndDate(rs.getString("EndDate"));
                // tourAllBooking.setStartDate(sdf.format(rs.getDate("StartDate")));
                // tourAllBooking.setEndDate(sdf.format(rs.getDate("EndDate")));
                tourAllBooking.setDateBooking(rs.getString("DateBooking"));
                tourAllBooking.setTourCancel(rs.getString("TourCancel"));
                tourAllBooking.setDateCancel(rs.getString("DateCancel"));
                getAllTourBooking.add(tourAllBooking);     
            }
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
            
        }
        return getAllTourBooking;
    }
    
    
    public Collection getAllTourCancel() {
        
        getAllTourCancel=new ArrayList();
        conn=con.getConnection();
        try {
            
            cl=conn.prepareCall("{call getAllTourCancel_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                TourBooking tourCancel=new TourBooking();
                tourCancel.setBookingId(rs.getInt("BookingID"));
                tourCancel.setUserName(rs.getString("UserName"));
                tourCancel.setTourId(rs.getString("TourID"));
                tourCancel.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                tourCancel.setTypeTour(rs.getString("TypeTour"));
                tourCancel.setTotalMoney(rs.getFloat("TotalMoney"));
                tourCancel.setStartDate(rs.getString("StartDate"));
                tourCancel.setEndDate(rs.getString("EndDate"));
                tourCancel.setDateBooking(rs.getString("DateBooking"));
                tourCancel.setTourCancel(rs.getString("TourCancel"));
                tourCancel.setDateCancel(rs.getString("DateCancel"));
                getAllTourCancel.add(tourCancel);
                
            }
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();;
        }
        return getAllTourCancel;
    }
    public Collection getBillCancelTour(String user) {
        
        conn=con.getConnection();
        getBillCancel=new ArrayList();
        try {
            cl=conn.prepareCall("{call getBillCancelTour_sp(?)}");
            cl.setString(1,user);
            ResultSet rs=cl.executeQuery();
            
            while(rs.next()) {
                TourBooking tourCancel=new TourBooking();
                tourCancel.setBookingId(rs.getInt("BookingID"));
                tourCancel.setTourId(rs.getString("TourID"));
                tourCancel.setUserName(rs.getString("UserName"));
                tourCancel.setTypeTour(rs.getString("TypeTour"));
                tourCancel.setNumberOfChild(rs.getInt("NumberOfChild"));
                tourCancel.setNumberOfAdult(rs.getInt("NumberOfAdult"));
                tourCancel.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                tourCancel.setTotalMoney(rs.getFloat("TotalMoney"));
                tourCancel.setStartDate(rs.getString("StartDate"));
                tourCancel.setEndDate(rs.getString("EndDate"));
                tourCancel.setDateBooking(rs.getString("DateBooking"));
                tourCancel.setTourCancel(rs.getString("TourCancel"));
                tourCancel.setCash(rs.getString("Cash"));
                tourCancel.setDateCancel(rs.getString("DateCancel"));
                tourCancel.setRefundMoney(rs.getFloat("refundMoney"));
                getBillCancel.add(tourCancel);
            }   
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }return getBillCancel;
    }
    
    public Collection getAllTourBook() {
        
        getAllTourBook=new ArrayList();
        conn=con.getConnection();
        try {
            
            cl=conn.prepareCall("{call getAllTourBook_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                TourBooking tourBook=new TourBooking();
                tourBook.setBookingId(rs.getInt("BookingID"));
                tourBook.setUserName(rs.getString("UserName"));
                tourBook.setTourId(rs.getString("TourID"));
                tourBook.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                tourBook.setTypeTour(rs.getString("TypeTour"));
                tourBook.setTotalMoney(rs.getFloat("TotalMoney"));
                tourBook.setStartDate(rs.getString("StartDate"));
                tourBook.setEndDate(rs.getString("EndDate"));
                tourBook.setDateBooking(rs.getString("DateBooking"));
                
                getAllTourBook.add(tourBook);
                
            }
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();;
        }
        return getAllTourBook;
    }
    
    public boolean InsertTourBooking(String tourID,String username,String TourType,int numberOfChild,int numberOfAdult,int totalOfguest,float totalMoney,String startDate,String endDate,String dateBooking,String tourCancel,String cash) {
        Statement st;
        conn=con.getConnection();
        try {
            cl=conn.prepareCall("{call InsertTourBooking_sp(?,?,?,?,?,?,?,?,?,?,?,?)}");
            cl.setString("UserName",username);
            cl.setString("TourID",tourID);
            cl.setString("TypeTour",TourType);
            cl.setInt("NumberOfChild",numberOfChild);
            cl.setInt("NumberOfAdult", numberOfAdult);
            cl.setInt("TotalOfGuest", totalOfguest);
            cl.setFloat("TotalMoney",totalMoney);
            cl.setString("StartDate", startDate);
            cl.setString("EndDate", endDate);
            cl.setString("DateBooking", dateBooking);
            cl.setString("TourCancel", tourCancel);
            cl.setString("Cash", cash);
            int row=cl.executeUpdate();
            if(row>0) {
                return true;
                
            }
           closeCnn();
        } catch (SQLException ex) {
            closeCnn();
            ex.printStackTrace();
        }return false;
    }
    
    public Collection getBillBookTour(String user) {
        
        conn=con.getConnection();
        getBillBookTour=new ArrayList();
        try {
            cl=conn.prepareCall("{call getBillBookTour_sp(?)}");
            cl.setString(1,user);
            ResultSet rs=cl.executeQuery();
            
            while(rs.next()) {
                TourBooking bookTour=new TourBooking();
                bookTour.setBookingId(rs.getInt("BookingID"));
                bookTour.setTourId(rs.getString("TourID"));
                bookTour.setUserName(rs.getString("UserName"));
                bookTour.setTypeTour(rs.getString("TypeTour"));
                bookTour.setNumberOfChild(rs.getInt("NumberOfChild"));
                bookTour.setNumberOfAdult(rs.getInt("NumberOfAdult"));
                bookTour.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                bookTour.setTotalMoney(rs.getFloat("TotalMoney"));
                bookTour.setStartDate(rs.getString("StartDate"));
                bookTour.setEndDate(rs.getString("EndDate"));
                bookTour.setDateBooking(rs.getString("DateBooking"));
                bookTour.setTourCancel(rs.getString("TourCancel"));
                bookTour.setCash(rs.getString("Cash"));
                
                getBillBookTour.add(bookTour);
            }  
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }return getBillBookTour;
    }
    
    
    
    
    public boolean CancelTourBooking(String username,int TourBookingId,String tourCanel,String DateCan,float  refunMoney) {
        conn=con.getConnection();
        try {
            cl=conn.prepareCall("{call CancelTourBooking_sp(?,?,?,?,?)}");
            cl.setString("TourCancel",tourCanel);
            cl.setString("DateCancel",DateCan);
            cl.setFloat("refundMoney",refunMoney);
            cl.setInt("BookingID",TourBookingId);
            cl.setString("userName",username);
            int row=cl.executeUpdate();
            if(row>0) {
                return true;
            }
            closeCnn();
            
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();;
        }return false;
    }
    
    public int getDayOfTour(String username,String idtourBoking) {
        conn=con.getConnection();
        int day=0;
        try {
            cl=conn.prepareCall("{call getDayOfTour_sp(?,?)}");
            cl.setString("userName",username);
            cl.setInt("BookingID",Integer.valueOf(idtourBoking));
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                day=rs.getInt("numberDay");
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
        return day;
    }
    
    public Collection getBookingId(int id) {
        conn=con.getConnection();
        getBookingId=new ArrayList();
        
        try {
           cl=conn.prepareCall("{call getBookingId_sp(?)}");
           cl.setInt(1,id);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
            TourBooking bookTour=new TourBooking();
                bookTour.setBookingId(rs.getInt("BookingID"));
                bookTour.setTourId(rs.getString("TourID"));
                bookTour.setUserName(rs.getString("UserName"));
                bookTour.setTypeTour(rs.getString("TypeTour"));
                bookTour.setNumberOfChild(rs.getInt("NumberOfChild"));
                bookTour.setNumberOfAdult(rs.getInt("NumberOfAdult"));
                bookTour.setTotalOfGuest(rs.getInt("TotalOfGuest"));
                bookTour.setTotalMoney(rs.getFloat("TotalMoney"));
                bookTour.setStartDate(rs.getString("StartDate"));
                bookTour.setEndDate(rs.getString("EndDate"));
                bookTour.setDateBooking(rs.getString("DateBooking"));               
                bookTour.setCash(rs.getString("Cash"));
                getBookingId.add((Object)bookTour);
           } 
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }return getBookingId;
    }
}
